﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EntitiesLayer;
using System.Collections;
using System.Transactions;
using System.Data.SqlClient;
using System.Data;
using DAL.Repositories;
using DAL;

namespace DAL.Repositories
{
    public class RolRepository : IRepository<Rol>
    {
            private List<IEntity> _insertItems;
            private List<IEntity> _updateItems;

            public RolRepository()
            {
                _insertItems = new List<IEntity>();
                _updateItems = new List<IEntity>();
            }

            public void Insert(Rol entity)
            {
                _insertItems.Add(entity);
            }

            public void Update(Rol entity)
            {
                _updateItems.Add(entity);
            }


            public IEnumerable<Rol> GetAll()
            {


                List<Rol> listaRoles = null;

                SqlCommand cmd = new SqlCommand();
                DataSet ds = DBAccess.ExecuteSPWithDS(ref cmd, "pa_listar_Roles");

                if (ds.Tables[0].Rows.Count > 0)
                {
                    listaRoles = new List<Rol>();

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        listaRoles.Add(new Rol
                        {
                            Id = Convert.ToInt32(dr["id"]),
                            Nombre = dr["nombre"].ToString(),
                            Descripcion = dr["descripcion"].ToString()
                            
                        });
                    }
                }

                return listaRoles;
            }


            public IEnumerable<int> GetPermisos(int pid)
            {


                List<int> listaPermisos = null;

                SqlCommand cmd = new SqlCommand();
                cmd.Parameters.Add(new SqlParameter("@idRol", pid));
                DataSet ds = DBAccess.ExecuteSPWithDS(ref cmd, "pa_listar_permisosAsociadosARol");

                if (ds.Tables[0].Rows.Count > 0)
                {
                    listaPermisos = new List<int>();

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        listaPermisos.Add( Convert.ToInt32(dr["codigo"]));
                    }
                }

                return listaPermisos;
            }
        
        
        public Rol GetById(int id)
            {
                Rol objRol = null;
                var sqlQuery = "SELECT id, nombre, descripcion FROM Roles WHERE id = @id";
                SqlCommand cmd = new SqlCommand(sqlQuery);
                cmd.Parameters.AddWithValue("@id", id);

                var ds = DBAccess.ExecuteQuery(cmd);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    var dr = ds.Tables[0].Rows[0];

                    objRol = new Rol
                    {
                        Id = Convert.ToInt32(dr["id"]),
                        Nombre = dr["nombre"].ToString(),
                        Descripcion = dr["descripcion"].ToString()
                    };
                }

                return objRol;
            }

            public void Save()
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    try
                    {
                        if (_insertItems.Count > 0)
                        {
                            foreach (Rol objRol in _insertItems)
                            {
                                InsertRol(objRol);
                            }
                        }

                        if (_updateItems.Count > 0)
                        {
                            foreach (Rol p in _updateItems)
                            {
                                UpdateRol(p);
                            }
                        }


                        scope.Complete();
                    }
                    catch (TransactionAbortedException ex)
                    {

                    }
                    catch (ApplicationException ex)
                    {

                    }
                    finally
                    {
                        Clear();
                    }

                }
            }

            public void Clear()
            {
                _insertItems.Clear();
                _updateItems.Clear();
            }


            private void InsertRol(Rol objRol)
            {

                try
                {
                   
                    SqlCommand cmd1 = new SqlCommand();
                    SqlCommand cmd2 = new SqlCommand();

                    cmd1.Parameters.Add(new SqlParameter("@nombre", objRol.Nombre));
                    cmd1.Parameters.Add(new SqlParameter("@descripcion", objRol.Descripcion));
                    DataSet ds = DBAccess.ExecuteSPWithDS(ref cmd1, "pa_agregar_rol");

                    foreach (int codigoPermiso in objRol.ListaPermisos)
                    {
                        cmd2.Parameters.Add(new SqlParameter("@codigoPermiso", codigoPermiso));
                        DataSet ds1 = DBAccess.ExecuteSPWithDS(ref cmd2, "pa_asociar_permisos");
                        cmd2.Parameters.Clear();
                    }



                }
                catch (Exception ex)
                {

                }

            }

            private void UpdateRol(Rol objRol)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    SqlCommand cmd1 = new SqlCommand();

                    cmd.Parameters.Add(new SqlParameter("@idRol", objRol.Id));
                    cmd.Parameters.Add(new SqlParameter("@nombre", objRol.Nombre));
                    cmd.Parameters.Add(new SqlParameter("@descripcion", objRol.Descripcion));

                    DataSet ds = DBAccess.ExecuteSPWithDS(ref cmd, "pa_modificar_rol");

                    foreach (int codigoPermiso in objRol.ListaPermisos)
                    {
                        cmd1.Parameters.Add(new SqlParameter("@codigoPermiso", codigoPermiso));
                        cmd1.Parameters.Add(new SqlParameter("@idRol", objRol.Id));
                        DataSet ds1 = DBAccess.ExecuteSPWithDS(ref cmd1, "pa_modificar_asociar_permisos");
                        cmd1.Parameters.Clear();
                    }


                }
                catch (Exception ex)
                {

                }
            }

            public IEnumerable<Rol> FiltrarListaRoles(String ptextoAfiltrar)
            {
                List<Rol> listaRoles = null;

                SqlCommand cmd = new SqlCommand();

                cmd.Parameters.Add(new SqlParameter("@textoAfiltrar", ptextoAfiltrar));
                DataSet ds = DBAccess.ExecuteSPWithDS(ref cmd, "pa_filtrar_Roles");

                if (ds.Tables[0].Rows.Count > 0)
                {
                    listaRoles = new List<Rol>();

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {

                        listaRoles.Add(new Rol
                        {
                            Id = Convert.ToInt32(dr["id"]),
                            Nombre = dr["nombre"].ToString(),
                            Descripcion = dr["descripcion"].ToString(),

                        });
                    }
                }

                return listaRoles;
            }



    }
}
